﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Linq;
using System.Data.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.Utils;
using DevEx.Thanh.Class;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;

namespace DevEx.QuocSy.UserControl
{
    public partial class ucBaoCaoNhapXuat : DevExpress.XtraEditors.XtraUserControl
    {
        QLVTDataContext dc = new QLVTDataContext();
        String _NX = "N";
        int _ID_DonVi = 0;
        IQueryable listPhieu;

        public ucBaoCaoNhapXuat(String NX)
        {
            InitializeComponent();
            _NX = NX;
        }

        private void ucBaoCaoNhapXuat_Load(object sender, EventArgs e)
        {
            KhoiTaoDanhMuc();
        }
        
        public void KhoiTaoDanhMuc()
        {
            //1. Danh muc Tinh chat Nhap
            CTCNX m_TCNX = new CTCNX();
            if (_NX == "N")
            {
                lblDonVi.Text = "Đơn vị bán";
                lblTCNX.Text = "TC Nhập";
                lblTieuDe.Text = "BÁO CÁO TỔNG HỢP HÀNG NHẬP";
                lblTieuDe.ForeColor = Color.LimeGreen;

                lkeTCNhap.Properties.DisplayMember = "TCNX";
                lkeTCNhap.Properties.ValueMember = "ID_TCNX";
                lkeTCNhap.Properties.DataSource = m_TCNX.SelectAllRecord(true);
            }
            else if(_NX == "X")
            {
                lblDonVi.Text = "Đơn vị nhận";
                lblTCNX.Text = "TC Xuất";
                lblTieuDe.Text = "BÁO CÁO TỔNG HỢP HÀNG XUẤT";
                lblTieuDe.ForeColor = Color.Red;

                lkeTCNhap.Properties.DisplayMember = "TCNX";
                lkeTCNhap.Properties.ValueMember = "ID_TCNX";
                lkeTCNhap.Properties.DataSource = m_TCNX.SelectAllRecord(false);
            }            

            //2. Danh muc Kho
            CKho m_kho = new CKho();
            lkeKho.Properties.DisplayMember = "Kho";
            lkeKho.Properties.ValueMember = "ID_Kho";
            lkeKho.Properties.DataSource = m_kho.SelectAllRecord();            
            lkeKho.EditValue = GlobalVar.CurrentUser.ID_Kho;
            

            //3. Danh muc Hop Dong
            //CHopDong m_hopdong = new CHopDong();
            //lkeHopDong.Properties.DisplayMember = "SoHopDong";
            //lkeHopDong.Properties.ValueMember = "ID_HopDong";
            //lkeHopDong.Properties.DataSource = m_hopdong.SelectAllRecord();

            //4. Danh muc Nguon Vat Tu
            CNguonVatTu m_nguonvattu = new CNguonVatTu();
            lkeNguonHT.Properties.DisplayMember = "NguonVatTu";
            lkeNguonHT.Properties.ValueMember = "ID_NguonVatTu";
            lkeNguonHT.Properties.DataSource = m_nguonvattu.SelectAllRecord();
        }
        
        public void FormatdtgBaoCaoNhapXuat()
        {
            gridView1.Columns["ID_DPNX"].Visible = false;
            gridView1.Columns["NX"].Visible = false;
            gridView1.Columns["ID_DonVi"].Visible = false;
            gridView1.Columns["ID_Kho"].Visible = false;
            gridView1.Columns["ID_NguonVatTu"].Visible = false;
            gridView1.Columns["ID_TCNX"].Visible = false;
            gridView1.Columns["NgayPhieu"].Visible = false;
            gridView1.Columns["SoPhieu"].Visible = false;
            //gridView1.Columns["ID_SanPham"].Visible = false;
            //gridView1.Columns["ID_HopDong"].Visible = false;
            
            gridView1.Columns["Phieu"].Group();
            gridView1.Columns["Phieu"].AppearanceCell.ForeColor = Color.Blue;
            gridView1.ExpandAllGroups();            
            
            var cl_DVT = gridView1.Columns.ColumnByFieldName("DVT");
            cl_DVT.Width = 50;
            cl_DVT.Caption = "ĐVT";
            cl_DVT.OptionsColumn.AllowEdit = false;
            cl_DVT.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_DVT.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //var cl_SoHopDong = gridView1.Columns.ColumnByFieldName("SoHopDong");
            //cl_SoHopDong.Width = 80;
            //cl_SoHopDong.Caption = "Nguồn gốc";
            //cl_SoHopDong.OptionsColumn.AllowEdit = false;
            //cl_SoHopDong.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            //cl_SoHopDong.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            var cl_VT = gridView1.Columns.ColumnByFieldName("VatTu");
            cl_VT.Width = 200;
            cl_VT.Caption = "Vật tư";
            cl_VT.OptionsColumn.AllowEdit = false;
            cl_VT.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Near;
            cl_VT.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            var cl_MKH = gridView1.Columns.ColumnByFieldName("MaKyHieu");
            cl_MKH.Width = 150;
            cl_MKH.Caption = "Mã ký hiệu";
            cl_MKH.OptionsColumn.AllowEdit = false;
            cl_MKH.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Near;
            cl_MKH.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            var cl_NVT = gridView1.Columns.ColumnByFieldName("KH_NguonVatTu");
            cl_NVT.Width = 60;
            cl_NVT.Caption = "Nguồn HT";
            cl_NVT.OptionsColumn.AllowEdit = false;
            cl_NVT.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_NVT.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            var cl_SoTheKho = gridView1.Columns.ColumnByFieldName("SoTheKho");
            cl_SoTheKho.Width = 80;
            cl_SoTheKho.Caption = "Số thẻ kho";
            cl_SoTheKho.OptionsColumn.AllowEdit = false;
            cl_SoTheKho.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_SoTheKho.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            var cl_ViTri = gridView1.Columns.ColumnByFieldName("ViTri");
            cl_ViTri.Width = 80;
            cl_ViTri.Caption = "Vị trí";
            cl_ViTri.OptionsColumn.AllowEdit = false;
            cl_ViTri.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_ViTri.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            ////Columns NgayPhieu
            //var cl_NgayPhieu = gridView1.Columns.ColumnByFieldName("NgayPhieu");
            //cl_NgayPhieu.Width = 80;
            //cl_NgayPhieu.Caption = "Ngày Phiếu";
            //cl_NgayPhieu.OptionsColumn.AllowEdit = false;
            //cl_NgayPhieu.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            //cl_NgayPhieu.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns HopDong
            var cl_HopDong = gridView1.Columns.ColumnByFieldName("SoHopDong");
            cl_HopDong.Width = 100;
            cl_HopDong.Caption = "Nguồn gốc"; 
            cl_HopDong.OptionsColumn.AllowEdit = false;
            cl_HopDong.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_HopDong.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns ThanhTien
            var cl_SLKH = gridView1.Columns.ColumnByFieldName("SLKH");
            cl_SLKH.Width = 80;
            cl_SLKH.Caption = "SLKH";
            cl_SLKH.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_SLKH.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_SLKH.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_SLKH.DisplayFormat.FormatString = "#,###.##";

            //Columns ThanhTien
            var cl_ThanhTien = gridView1.Columns.ColumnByFieldName("ThanhTien");
            cl_ThanhTien.Width = 100;
            cl_ThanhTien.Caption = "Thành tiền";
            cl_ThanhTien.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Far;
            cl_ThanhTien.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_ThanhTien.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_ThanhTien.DisplayFormat.FormatString = "#,###.##";

            //Columns Gia
            var cl_Gia = gridView1.Columns.ColumnByFieldName("Gia");
            cl_Gia.Width = 80;
            cl_Gia.Caption = "Đơn giá";
            cl_Gia.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Far;
            cl_Gia.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_Gia.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_Gia.DisplayFormat.FormatString = "#,###.##";
        }

        public IQueryable LocDanhSachPhieu(DateTime ngayPhieuTu, DateTime ngayPhieuDen, int tinhChatNhapXuatID, int khoNhapXuatID, 
            int donViID, int nguonTCID)
        {
            //IQueryable<tblDPNX>
            //+ " - Ngày : " + Convert.ToDateTime(p.NgayPhieu).ToLongDateString()
            
            var listPhieu = from p in dc.tblDPNXes
                            join ct in dc.tblSLNXes on p.ID_DPNX equals ct.ID_DPNX
                            join t in dc.tblTons on ct.ID_MatHang equals t.ID_MatHang
                            join vt in dc.tblVatTus on t.ID_VatTu equals vt.ID_VatTu 
                            where p.NX == _NX
                            orderby p.NgayPhieu,p.SoPhieu descending
                            select new 
                            {
                                ID_DPNX = p.ID_DPNX, 
                                NX = p.NX,
                                SoPhieu = (Convert.ToDateTime(p.NgayPhieu).Month > 9 ?
                                Convert.ToString(p.SoPhieu) + p.DuoiPhieu + "/" + Convert.ToDateTime(p.NgayPhieu).Month.ToString() + "-" + Convert.ToDateTime(p.NgayPhieu).Year.ToString().Substring(2, 2)
                                : Convert.ToString(p.SoPhieu) + p.DuoiPhieu + "/0" + Convert.ToDateTime(p.NgayPhieu).Month.ToString() + "-" + Convert.ToDateTime(p.NgayPhieu).Year.ToString().Substring(2, 2)),                                
                                Phieu = "Số phiếu: " + (Convert.ToDateTime(p.NgayPhieu).Month > 9 ?
                                Convert.ToString(p.SoPhieu) + p.DuoiPhieu + "/" + Convert.ToDateTime(p.NgayPhieu).Month.ToString() + "-" + Convert.ToDateTime(p.NgayPhieu).Year.ToString().Substring(2, 2)
                                : Convert.ToString(p.SoPhieu) + p.DuoiPhieu + "/0" + Convert.ToDateTime(p.NgayPhieu).Month.ToString() + "-" + Convert.ToDateTime(p.NgayPhieu).Year.ToString().Substring(2, 2))
                                + " - Ngày : " + Convert.ToDateTime(p.NgayPhieu).Day.ToString() + "/" + Convert.ToDateTime(p.NgayPhieu).Month.ToString() + "/" + Convert.ToDateTime(p.NgayPhieu).Year.ToString().Substring(2, 2)
                                + " - Đơn vị giao nhận : " + p.tblDonVi.DonVi
                                + " - TCNX : " + p.tblTCNX.TCNX,
                                NgayPhieu = p.NgayPhieu,
                                ID_Kho = p.ID_Kho,
                                ID_TCNX = p.ID_TCNX,
                                ID_DonVi = p.ID_DonVi,                                
                                VatTu = vt.VatTu,
                                MaKyHieu = vt.MaKyHieu,
                                DVT = vt.tblDVT.DVT,
                                Gia = t.Gia,
                                SLKH = ct.SLKH,
                                ThanhTien = t.Gia * ct.SLKH,
                                ID_NguonVatTu = t.ID_NguonVatTu,
                                KH_NguonVatTu = t.tblNguonVatTu.KH_NguonVatTu,
                                SoTheKho = t.SoTheKho + "-" + t.Code.ToString(),
                                ViTri = t.TangKeKhoang,
                                SoHopDong = t.tblHopDong.SoHopDong ?? ""
                            };


            if (ngayPhieuTu != DateTime.MinValue)
            {
                listPhieu = listPhieu.Where(p => Convert.ToDateTime(p.NgayPhieu).Date >= ngayPhieuTu);
            }
            if (ngayPhieuDen != DateTime.MinValue)
            {
                listPhieu = listPhieu.Where(p => Convert.ToDateTime(p.NgayPhieu).Date <= ngayPhieuDen);
            }
            if (tinhChatNhapXuatID > 0)
            {
                listPhieu = listPhieu.Where(p => p.ID_TCNX == tinhChatNhapXuatID);
            }
            if (khoNhapXuatID > 0)
            {
                listPhieu = listPhieu.Where(p => p.ID_Kho == khoNhapXuatID);
            }
            
            if (donViID > 0)
            {
                listPhieu = listPhieu.Where(p => p.ID_DonVi == donViID);
            }

            if (nguonTCID > 0)
            {
                listPhieu = listPhieu.Where(p => p.ID_NguonVatTu == nguonTCID);
            }

            

            //if (nguonTCID > 0)
            //{
            //    listPhieu = listPhieu.Where(p => p.tblSLNXes.Any(ct => ct.tblTon.ID_NguonVatTu == nguonTCID));
            //}

            //dinh dang lai du lieu
            //var k = from h in listPhieu
            //         orderby h.NgayPhieu descending
            //         select h;
            //MessageBox.Show(listPhieu.Count().ToString());
            return listPhieu;
        }

        private void btnBaoCao_Click(object sender, EventArgs e)
        {
            
            DateTime ngayPhieuTu = DateTime.MinValue;
            DateTime ngayPhieuDen = DateTime.MinValue;
            
            if (dateNgayPhieuTu.Text != "")
                ngayPhieuTu = Convert.ToDateTime(dateNgayPhieuTu.EditValue).Date;
            if (dateNgayPhieuDen.Text != "")
                ngayPhieuDen = Convert.ToDateTime(dateNgayPhieuDen.EditValue).Date;

            int tinhChatNhapXuatID = 0;
            if (lkeTCNhap.EditValue != null)
                tinhChatNhapXuatID = Convert.ToInt32(lkeTCNhap.EditValue);
            

            int khoNhapXuatID = Convert.ToInt32(lkeKho.EditValue);            
            
            
            int nguonTCID = 0;
            if (lkeNguonHT.EditValue != null)
                nguonTCID = Convert.ToInt32(lkeNguonHT.EditValue);

                        
            listPhieu = LocDanhSachPhieu(ngayPhieuTu, ngayPhieuDen, tinhChatNhapXuatID, khoNhapXuatID, _ID_DonVi, nguonTCID);
            try
            {

                dtgBaoCaoNhapXuat.DataSource = listPhieu;
                FormatdtgBaoCaoNhapXuat();
            }
            catch { };           
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            lkeKho.EditValue = GlobalVar.CurrentUser.ID_Kho;
            lkeNguonHT.EditValue = null;
            lkeTCNhap.EditValue = null;
            dateNgayPhieuDen.EditValue = null;
            dateNgayPhieuTu.EditValue = null;
            _ID_DonVi = 0;
            txtDonViBan.EditValue = "";
            btnBaoCao_Click(sender, e);
        }

        #region Format lke
        private void lkeTCNhap_VisibleChanged(object sender, EventArgs e)
        {
            lkeTCNhap.Properties.PopulateColumns();
            lkeTCNhap.Properties.Columns["ID_TCNX"].Visible = false;
            lkeTCNhap.Properties.Columns["IsNhap"].Visible = false;
        }

        private void lkeKho_VisibleChanged(object sender, EventArgs e)
        {
            lkeKho.Properties.PopulateColumns();
            lkeKho.Properties.Columns["ID_Kho"].Visible = false;
            lkeKho.Properties.Columns["ID_DonVi"].Visible = false;
            lkeKho.Properties.Columns["KH_Kho"].Visible = false;
        }

        private void lkeNguonHT_VisibleChanged(object sender, EventArgs e)
        {
            lkeNguonHT.Properties.PopulateColumns();
            lkeNguonHT.Properties.Columns["ID_NguonVatTu"].Visible = false;
            lkeNguonHT.Properties.Columns["KH_NguonVatTu"].Visible = false;
        }

        private void lkeNguonHT_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete)
            {
                lkeNguonHT.ClosePopup();
                lkeNguonHT.EditValue = null;
                e.Handled = true;
            }
        }

        private void lkeTCNhap_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete)
            {
                lkeTCNhap.ClosePopup();
                lkeTCNhap.EditValue = null;
                e.Handled = true;
            }
        }
        #endregion
        
        #region AutoComplete DonViBan

        AutoCompleteStringCollection donviSource = new AutoCompleteStringCollection();

        public void KhoiTaoAutoCompleteDonVi()
        {
            var listDonVi = from p in dc.tblDonVis
                            where p.Cuoi == true //&& !(from q in dc.tblKhos select q.ID_DonVi).Contains(p.ID_DonVi)
                            orderby p.DonVi
                            select p;

            //var listKho = from q in dc.tblKhos
            //              select q;

            foreach (tblDonVi donvi in listDonVi)
                donviSource.Add(donvi.DonVi);

            txtDonViBan.MaskBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
            txtDonViBan.MaskBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            txtDonViBan.MaskBox.AutoCompleteCustomSource = donviSource;
        }

        private void txtDonViBan_Enter(object sender, EventArgs e)
        {
            KhoiTaoAutoCompleteDonVi();
        }

        private void txtDonViBan_Leave(object sender, EventArgs e)
        {
            CDonVi m_donvi = new CDonVi();
            tblDonVi objDonVi = m_donvi.SelectRecordByName(Convert.ToString(txtDonViBan.EditValue));
            if (objDonVi == null)
            {
                txtDonViBan.Text = "";
                _ID_DonVi = 0;
            }
            else
            {
                _ID_DonVi = objDonVi.ID_DonVi;
                txtDonViBan.EditValue = objDonVi.DonVi;
            }
        }       
        #endregion

        private void btnExcel_Click(object sender, EventArgs e)
        {
            QLVTDataContext dc = new QLVTDataContext();
            //Chuyen so lieu tu excel sang store and don vi
            //Microsoft.Office.Interop.Excel._Application oXL; 
            object xlApp;

            //Excel._Application xlApp;
            Workbook xlBook; 
            Worksheet xlSheet;

            //xlApp = new Excel._Application();
            Type t = Type.GetTypeFromProgID("Excel.Application");
            xlApp = System.Activator.CreateInstance(t);

            String link = System.Windows.Forms.Application.StartupPath + "\\TONGHOPNHAPXUAT_template.xltx";
            xlBook = ((Microsoft.Office.Interop.Excel.Application)xlApp).Workbooks.Open(link);
            xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            ((Microsoft.Office.Interop.Excel.Application)xlApp).Visible = true;

            // TIÊU ĐỀ
            String TieuDe = "";
            String NgayBaoCao = "";

            if(_NX == "N")
            {
                TieuDe = "BÁO CÁO TỔNG HỢP HÀNG NHẬP " + lkeKho.Text.ToUpper();
            }
            else if(_NX == "X")
            {
                TieuDe = "BÁO CÁO TỔNG HỢP HÀNG XUẤT " + lkeKho.Text.ToUpper();
            }

            if ((Convert.ToDateTime(dateNgayPhieuTu.EditValue) != DateTime.MinValue) && (Convert.ToDateTime(dateNgayPhieuDen.EditValue) != DateTime.MinValue))
            {
                NgayBaoCao = "(Từ ngày " + Convert.ToDateTime(dateNgayPhieuTu.EditValue).ToString("dd/MM/yyyy") + " đến ngày " + Convert.ToDateTime(dateNgayPhieuDen.EditValue).ToString("dd/MM/yyyy") + ")";
            }
            else
                NgayBaoCao = "";

            xlSheet.Cells[1, 1] = TieuDe;
            xlSheet.Cells[2, 1] = NgayBaoCao;

            int row0 = 5;
            int i = 1;
            Excel.Range mRange;
            foreach(dynamic item in listPhieu)
            {
                //1. STT                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 1]];
                mRange.Value = i;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //2. Vat Tu                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 2], xlSheet.Cells[row0 + i, 2]];
                mRange.Value = item.VatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //3. Ma Ky Hieu                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 3], xlSheet.Cells[row0 + i, 3]];
                mRange.Value = item.MaKyHieu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //4. DVT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 4], xlSheet.Cells[row0 + i, 4]];
                mRange.Value = item.DVT;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //5. Gia
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 5], xlSheet.Cells[row0 + i, 5]];
                mRange.Value = item.Gia;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                //6. SLKH
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 6], xlSheet.Cells[row0 + i, 6]];
                mRange.Value = item.SLKH;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                mRange.NumberFormat = "#,###";

                //7. ThanhTien
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 7], xlSheet.Cells[row0 + i, 7]];
                mRange.Value = item.ThanhTien;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                //8. Nguồn hạch toán
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 8], xlSheet.Cells[row0 + i, 8]];
                mRange.Value = item.KH_NguonVatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //9. So Phieu
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 9], xlSheet.Cells[row0 + i, 9]];
                mRange.Value = item.SoPhieu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //10. Ngay Phieu
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 10], xlSheet.Cells[row0 + i, 10]];
                mRange.Value = Convert.ToDateTime(item.NgayPhieu).ToString("dd/MM/yyyy");
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //11. So Hop Dong (Nguon Goc)
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 11], xlSheet.Cells[row0 + i, 11]];
                mRange.Value = item.SoHopDong;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                // Border
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 12]];
                var border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                border.LineStyle = Excel.XlLineStyle.xlDot;
                border.Weight = Excel.XlBorderWeight.xlThin;

                i++;
            }

            // Border cuối
            mRange = xlSheet.Range[xlSheet.Cells[row0 + i - 1, 1], xlSheet.Cells[row0 + i - 1, 12]];
            var border_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            border_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_cuoi.Weight = Excel.XlBorderWeight.xlThin;

            // Border các cột
            for (int col = 1; col <= 13; col++)
            {
                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + i - 1, col]];
                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                border_col.Weight = Excel.XlBorderWeight.xlThin;
            }

              
            //I. DPNX
            //CDPNX m_DPNX = new CDPNX();
            //tblDPNX objDPNX = m_DPNX.SelectRecordByID(ID_DPNX);
            ////Số phiếu
            //String SoPhieu;
            //SoPhieu = GetSoPhieu(ID_DPNX);
            //xlSheet.Cells[3, 2] = "Số N: " + SoPhieu;

            ////Người nhập
            //xlSheet.Cells[4, 6] = objDPNX.NguoiGiaoNhan;
            ////Đơn vị bán
            //xlSheet.Cells[5, 6] = objDPNX.tblDonVi.DonVi;
            ////Địa chỉ
            //xlSheet.Cells[6, 6] = objDPNX.tblDonVi.DiaChi;
            ////Hoá đơn bán hàng
            //xlSheet.Cells[7, 6] = objDPNX.HoaDon;
            ////Kho nhập
            //xlSheet.Cells[8, 6] = objDPNX.tblKho.Kho;
            ////Mục đích - Lý do nhập xuất
            //xlSheet.Cells[9, 6] = objDPNX.LyDoNhapXuat;

            ////II. SLNX
            //CSLNX m_SLNX = new CSLNX();
            //var listSLNX = m_SLNX.SelectAllRecord(ID_DPNX);

            
            //int i = 1;
            //int dongdem = 0; // số dòng trắng thêm vào để qua trang.
            //Excel.Range mRange;

            //foreach (tblSLNX objSLNX in listSLNX)
            //{
            //    xlSheet.Cells[row0 + i * 2 - 1, 1] = i;
            //    //VATTU
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 2], xlSheet.Cells[row0 + i * 2 - 1, 2]];
            //    mRange.Value = objSLNX.tblTon.tblVatTu.VatTu;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            //    //MAKYHIEU
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 2], xlSheet.Cells[row0 + i * 2, 2]];
            //    mRange.Value = objSLNX.tblTon.tblVatTu.MaKyHieu;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            //    //DVT
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 5], xlSheet.Cells[row0 + i * 2 - 1, 5]];
            //    mRange.Value = objSLNX.tblTon.tblVatTu.tblDVT.DVT;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            //    //GIA 6
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 6], xlSheet.Cells[row0 + i * 2 - 1, 6]];
            //    mRange.Value = objSLNX.tblTon.Gia;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            //    mRange.NumberFormat = "#,###";

            //    //SLKH 7
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 7], xlSheet.Cells[row0 + i * 2 - 1, 7]];
            //    mRange.Value = objSLNX.SLKH;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //    mRange.NumberFormat = "#,###";

            //    //ThanhTien 8 - 9 Merge
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 8], xlSheet.Cells[row0 + i * 2 - 1, 9]];
            //    mRange.Merge();
            //    mRange.Value = objSLNX.ThanhTien;
            //    mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            //    mRange.NumberFormat = "#,###";

            //    xlSheet.Cells[row0 + i * 2 - 1, 10] = objSLNX.tblTon.tblNguonVatTu.KH_NguonVatTu;
            //    xlSheet.Cells[row0 + i * 2 - 1, 11] = objSLNX.tblTon.TangKeKhoang;
            //    xlSheet.Cells[row0 + i * 2 - 1, 12] = objSLNX.tblTon.SoTheKho + "-" + objSLNX.tblTon.Code.ToString();
            //    xlSheet.Cells[row0 + i * 2 - 1, 13] = objSLNX.tblTon.tblHopDong.SoHopDong;

            //    // Border
            //    mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
            //    var border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            //    border.LineStyle = Excel.XlLineStyle.xlDot;
            //    border.Weight = Excel.XlBorderWeight.xlThin;

            //    i++;

            //}

            //// Border cuối
            //mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2, 1], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
            //var border_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            //border_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            //border_cuoi.Weight = Excel.XlBorderWeight.xlThin;

            //// Border các cột
            //for (int col = 1; col <= 14; col++)
            //{
            //    if (!((col == 3) || (col == 4) || (col == 9)))
            //    {
            //        mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
            //        var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
            //        border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
            //        border_col.Weight = Excel.XlBorderWeight.xlThin;
            //    }

            //}

            ////III. DUOI PHIEU
            //i--;

            //int k;
            //if (i < 13)
            //    k = 36;
            //else
            //    k = row0 + i * 2 + 1;

            //mRange = xlSheet.Range[xlSheet.Cells[k, 2], xlSheet.Cells[k, 2]];
            //mRange.Value = "TỔNG CỘNG :";
            //mRange.Font.Bold = true;
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            //mRange = xlSheet.Range[xlSheet.Cells[k, 8], xlSheet.Cells[k, 9]];
            //mRange.Merge();
            //mRange.Font.Bold = true;
            //mRange.Value = objDPNX.TongGiaTri;
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            //mRange.NumberFormat = "#,##0";


            //mRange = xlSheet.Range[xlSheet.Cells[k + 1, 2], xlSheet.Cells[k + 1, 2]];
            //mRange.Value = "Số tiền bằng chữ :";
            //mRange.Font.Bold = true;
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            //mRange = xlSheet.Range[xlSheet.Cells[k + 1, 3], xlSheet.Cells[k + 1, 3]];
            //mRange.Font.Italic = true;
            //String TongTien = CThanh_Utilities.DocSo(Convert.ToDouble(objDPNX.TongGiaTri)).Trim();
            //if (TongTien.Length < 80)
            //    mRange.Value = TongTien;
            //else
            //{
            //    //> 80 xuống hàng
            //    int vitri = 0;
            //    for (int h = 0; h < TongTien.Length; h++)
            //    {
            //        if (TongTien.Substring(h, 1) == " ")
            //            vitri = h;

            //        if (h > 80)
            //            h = TongTien.Length;
            //    }
            //    mRange.Value = TongTien.Substring(0, vitri);
            //    mRange = xlSheet.Range[xlSheet.Cells[k + 2, 3], xlSheet.Cells[k + 2, 3]];
            //    mRange.Font.Italic = true;
            //    mRange.Value = TongTien.Substring(vitri + 1, TongTien.Length - vitri - 1);
            //}




            //String NgayPhieu;
            //NgayPhieu = "Ngày: " + Convert.ToDateTime(objDPNX.NgayPhieu).ToString("dd/MM/yyyy");
            //mRange = xlSheet.Range[xlSheet.Cells[k + 3, 9], xlSheet.Cells[k + 3, 12]];
            //mRange.Merge();
            //mRange.Font.Italic = true;
            //mRange.Value = NgayPhieu;
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


            //mRange = xlSheet.Range[xlSheet.Cells[k + 4, 2], xlSheet.Cells[k + 4, 2]];
            //mRange.Value = "NGƯỜI LẬP PHIẾU";
            //mRange.Font.Name = "Cambria";
            //mRange.Font.Bold = true;

            //mRange = xlSheet.Range[xlSheet.Cells[k + 4, 3], xlSheet.Cells[k + 4, 3]];
            //mRange.Value = "NGƯỜI GIAO";
            //mRange.Font.Name = "Cambria";
            //mRange.Font.Bold = true;

            //mRange = xlSheet.Range[xlSheet.Cells[k + 4, 5], xlSheet.Cells[k + 4, 5]];
            //mRange.Value = "THỦ KHO";
            //mRange.Font.Name = "Cambria";
            //mRange.Font.Bold = true;

            //mRange = xlSheet.Range[xlSheet.Cells[k + 4, 7], xlSheet.Cells[k + 4, 7]];
            //mRange.Value = "TP. VẬT TƯ";
            //mRange.Font.Name = "Cambria";
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //mRange.Font.Bold = true;

            //mRange = xlSheet.Range[xlSheet.Cells[k + 4, 9], xlSheet.Cells[k + 4, 12]];
            //mRange.Merge();
            //mRange.Value = "THỦ TRƯỞNG ĐƠN VỊ";
            //mRange.Font.Name = "Cambria";
            //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //mRange.Font.Bold = true;

            //    Dim iRowsCount = i - 1
            //Dim mColsCount = 12

            //'border for all
            //mRange = xlSheet.Range(xlSheet.Cells(iRow0, 1), xlSheet.Cells(iRow0 + iRowsCount, mColsCount)) '.Select()
            //With mRange.Borders(Excel.XlBordersIndex.xlEdgeLeft)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
            //With mRange.Borders(Excel.XlBordersIndex.xlEdgeTop)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
            //With mRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
            //With mRange.Borders(Excel.XlBordersIndex.xlEdgeRight)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
            //With mRange.Borders(Excel.XlBordersIndex.xlInsideVertical)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
            //With mRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
            //    .LineStyle = Excel.XlLineStyle.xlContinuous
            //    .Weight = Excel.XlBorderWeight.xlThin
            //End With
        }
    }
}
